﻿Imports MySql.Data.MySqlClient

Public Class CNilaiSiswa
    Inherits CContext

    Private INSERT_COMMAND As String
    Private UPDATE_COMMAND As String
    Private DELETE_COMMAND As String
    Private SELECT_COMMAND As String

    Public Sub New()
        MyBase.New()
    End Sub

    ''' <summary>
    ''' menampilkan daftar siswa (id,nama)
    ''' </summary>
    ''' <param name="idsemester"></param>
    ''' <param name="idkelas"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getDaftarSiswa(idsemester As Integer, idkelas As Integer) As DataTable
        SELECT_COMMAND = "select pk.id_ssw, s.nama from tbl_pembagian_kelas pk inner join tbl_siswa s on pk.id_ssw = s.id  where pk.id_smstr = " & idsemester & " and pk.id_kls = " & idkelas
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Function getPenilaian(idguru As Integer, idsemester As Integer, idkelas As Integer, idMataPelajaran As Integer) As DataTable
        SELECT_COMMAND = "select uj.id,uj.kat_nil,case uj.kat_nil when 3 then 'Tugas/Ulangan Harian' when 2 then 'UTS' when 1 then 'UAS' end as 'kat' , uj.tgl from tbl_nilai_ujian uj where uj.id_guru = " & idguru & " and uj.id_smstr = " & idsemester & " and uj.id_kls = " & idkelas & " and uj.id_mp = " & idMataPelajaran
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Function getPenilaian(idsemester As Integer, idkelas As Integer, idMataPelajaran As Integer) As DataTable
        SELECT_COMMAND = "select uj.id,uj.kat_nil,case uj.kat_nil when 3 then 'Tugas/Ulangan Harian' when 2 then 'UTS' when 1 then 'UAS' end as 'kat' , uj.tgl from tbl_nilai_ujian uj where uj.id_smstr = " & idsemester & " and uj.id_kls = " & idkelas & " and uj.id_mp = " & idMataPelajaran
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Function getNilaiSiswa(ByVal idsiswa As Integer, ByVal idujian As Integer, ByVal idmatapelajaran As Integer) As Integer
        SELECT_COMMAND = "select duj.id_ssw, duj.nil from tbl_detil_nilai_ujian duj inner join tbl_nilai_ujian uj on duj.id_nil_uj = uj.id where duj.id_ssw = " & idsiswa & " and uj.id =" & idujian & " and uj.id_mp=" & idmatapelajaran

        Try
            Return MyBase.SelectData(SELECT_COMMAND).Rows(0).Item("nil")
        Catch ex As IndexOutOfRangeException
            Return 0
        End Try

    End Function

    ''' <summary>
    ''' get daftar nilai siswa
    ''' </summary>
    ''' <param name="idPenilaian"></param>
    ''' <returns>id,id_ssw,nama,nil</returns>
    ''' <remarks></remarks>
    Public Function getNilaiSiswa(idPenilaian As Integer) As DataTable
        SELECT_COMMAND = "select dn.id_nil_uj as id ,dn.id_ssw,s.nama  ,dn.nil  from tbl_detil_nilai_ujian dn inner join tbl_siswa s on dn.id_ssw = s.id where dn.id_nil_uj = " & idPenilaian
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    ''' <summary>
    ''' insert data nilai
    ''' </summary>
    ''' <param name="idguru"></param>
    ''' <param name="idsemester"></param>
    ''' <param name="idkelas"></param>
    ''' <param name="idmatapelajaran"></param>
    ''' <param name="kategoriNilai"></param>
    ''' <param name="nilai">id_ssw,nil</param>
    ''' <remarks></remarks>
    Public Function simpan(idguru As Integer, idsemester As Integer, idkelas As Integer, idmatapelajaran As Integer, kategoriNilai As Integer, tanggal As Date, nilai As DataTable) As Integer
        Dim _TMysql As MySql.Data.MySqlClient.MySqlTransaction
        Dim _idMaster As Integer = Nothing
        MyBase.MySqlConnect.Open()
        _TMysql = MyBase.MySqlConnect.BeginTransaction

        Try


            'insert master nilai
            Dim INSERT_NILAI_UJIAN_COMMAND As MySqlCommand = MyBase.MySqlConnect.CreateCommand
            With INSERT_NILAI_UJIAN_COMMAND
                .CommandText = "INSERT INTO TBL_NILAI_UJIAN (ID_GURU,ID_SMSTR,ID_KLS,ID_MP,TGL,KAT_NIL) VALUES (?ID_GURU,?ID_SMSTR,?ID_KLS,?ID_MP,?TGL,?KAT_NIL)"
                .Parameters.Add(New MySqlParameter("ID_GURU", idguru))
                .Parameters.Add(New MySqlParameter("ID_SMSTR", idsemester))
                .Parameters.Add(New MySqlParameter("ID_KLS", idkelas))
                .Parameters.Add(New MySqlParameter("ID_MP", idmatapelajaran))
                .Parameters.Add(New MySqlParameter("TGL", tanggal))
                .Parameters.Add(New MySqlParameter("KAT_NIL", kategoriNilai))

                .Transaction = _TMysql
                .ExecuteNonQuery()
            End With

            'get id master nilai
            Dim SELECT_ID_NILAI_UJIAN_COMMAND As MySqlCommand = MyBase.MySqlConnect.CreateCommand
            Dim reader As MySqlDataReader
            Dim idMasterNilai As Integer = 0
            With SELECT_ID_NILAI_UJIAN_COMMAND
                .CommandText = "SELECT MAX(ID) as id FROM TBL_NILAI_UJIAN"

                .Transaction = _TMysql
                reader = .ExecuteReader
            End With
            reader.Read()
            idMasterNilai = reader.Item("id")
            _idMaster = idMasterNilai
            reader.Close()

            'insert detil
            For Each AROW As DataRow In nilai.Rows
                Dim INSERT_NILAI_UJIAN As MySqlCommand = MyBase.MySqlConnect.CreateCommand
                With INSERT_NILAI_UJIAN
                    .CommandText = "INSERT INTO TBL_DETIL_NILAI_UJIAN(ID_NIL_UJ,ID_SSW,NIL) VALUES (?ID_NIL_UJ,?ID_SSW,?NIL)"
                    .Parameters.Add(New MySqlParameter("ID_NIL_UJ", idMasterNilai))
                    .Parameters.Add(New MySqlParameter("ID_SSW", AROW.Item("id_ssw")))
                    .Parameters.Add(New MySqlParameter("NIL", AROW.Item("nil")))

                    .Transaction = _TMysql
                    .ExecuteNonQuery()
                End With
            Next

            'commit command
            _TMysql.Commit()

        Catch ex As Exception
            _TMysql.Rollback()
            MsgBox(ex.Message)
        Finally
            MyBase.MySqlConnect.Close()
        End Try

        Return _idMaster
    End Function

    ''' <summary>
    ''' update data nilai
    ''' </summary>
    ''' <param name="idpenilaian"></param>
    ''' <param name="datanilai">id_ssw,nil</param>
    ''' <remarks></remarks>
    Public Sub Update(idpenilaian As Integer, datanilai As DataTable)
        Dim _TMysql As MySql.Data.MySqlClient.MySqlTransaction
        MyBase.MySqlConnect.Open()
        _TMysql = MyBase.MySqlConnect.BeginTransaction

        Try


            'update detil
            For Each AROW As DataRow In datanilai.Rows
                Dim UPDATE_NILAI_UJIAN As MySqlCommand = MyBase.MySqlConnect.CreateCommand
                With UPDATE_NILAI_UJIAN
                    .CommandText = "update tbl_detil_nilai_ujian set nil = ?nil where id_nil_uj = ?id_nil_uj and id_ssw = ?id_ssw"
                    .Parameters.Add(New MySqlParameter("ID_NIL_UJ", idpenilaian))
                    .Parameters.Add(New MySqlParameter("ID_SSW", AROW.Item("id_ssw")))
                    .Parameters.Add(New MySqlParameter("NIL", AROW.Item("nil")))

                    .Transaction = _TMysql
                    .ExecuteNonQuery()
                End With
            Next

            'commit command
            _TMysql.Commit()
        Catch ex As Exception
            _TMysql.Rollback()
            MsgBox(ex.Message)
        Finally
            MyBase.MySqlConnect.Close()
        End Try

    End Sub

End Class
